{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 背景介绍\n",
    "- 某运营商为了回馈用户，发起了一个用户充值送视频会员的权益活动。\n",
    "- 活动具体为：\n",
    "    - 对于月租<=50元的用户只要充值30元话费就可以免费领取3个月的视频会员。\n",
    "    - 对于月租>50元的用户只要充值60元就可以免费领取6个月的视频会员。\n",
    "    - 该活动的仅仅为当年的9、10和11这三个月期间。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 然而在这3个月的活动中，外呼渗透率却逐渐下跌。因此，本次分析主要围绕两个问题展开：\n",
    "    - 1.为什么回馈用户活动的这3个月的完成情况越来越差？\n",
    "    - 2.哪些因素对用户充值话费办理视频会员的影响较大？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户号码</th>\n",
       "      <th>用户套餐月租</th>\n",
       "      <th>入网时间</th>\n",
       "      <th>近6个月平均话费</th>\n",
       "      <th>近6个月平均使用流量</th>\n",
       "      <th>近6个月平均使用语音</th>\n",
       "      <th>优惠名称</th>\n",
       "      <th>号码品牌</th>\n",
       "      <th>用户年龄</th>\n",
       "      <th>用户性别</th>\n",
       "      <th>是否订购</th>\n",
       "      <th>是否参与活动</th>\n",
       "      <th>活动开始时间</th>\n",
       "      <th>活动结束时间</th>\n",
       "      <th>外呼团队</th>\n",
       "      <th>外呼时间</th>\n",
       "      <th>外呼分钟数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>20020209</td>\n",
       "      <td>146.2050</td>\n",
       "      <td>9090.910500</td>\n",
       "      <td>398.3167</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>55</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201911</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>20060424</td>\n",
       "      <td>50.0000</td>\n",
       "      <td>3980.592767</td>\n",
       "      <td>86.9000</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>51</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201909</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>20111206</td>\n",
       "      <td>67.1125</td>\n",
       "      <td>1706.841767</td>\n",
       "      <td>453.0833</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>36</td>\n",
       "      <td>女</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>56</td>\n",
       "      <td>20120412</td>\n",
       "      <td>99.0000</td>\n",
       "      <td>2872.303067</td>\n",
       "      <td>41.3500</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>35</td>\n",
       "      <td>女</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>88</td>\n",
       "      <td>20150503</td>\n",
       "      <td>88.0000</td>\n",
       "      <td>28222.901100</td>\n",
       "      <td>326.3500</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>57</td>\n",
       "      <td>男</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   用户号码  用户套餐月租      入网时间  近6个月平均话费    近6个月平均使用流量  近6个月平均使用语音    优惠名称 号码品牌  \\\n",
       "0     1      56  20020209  146.2050   9090.910500    398.3167  送3个月会员   4G   \n",
       "1     2      50  20060424   50.0000   3980.592767     86.9000  送3个月会员   4G   \n",
       "2     3      50  20111206   67.1125   1706.841767    453.0833  送3个月会员   4G   \n",
       "3     4      56  20120412   99.0000   2872.303067     41.3500  送3个月会员   4G   \n",
       "4     5      88  20150503   88.0000  28222.901100    326.3500  送3个月会员   4G   \n",
       "\n",
       "   用户年龄 用户性别 是否订购   是否参与活动    活动开始时间    活动结束时间 外呼团队    外呼时间  外呼分钟数  \n",
       "0    55    男    否      NaN       NaN       NaN  NaN  201911     91  \n",
       "1    51    男    否      NaN       NaN       NaN  NaN  201909     28  \n",
       "2    36    女    是  会员赠送3个月  201909.0  202008.0  团队D  201909    128  \n",
       "3    35    女    是  会员赠送3个月  201909.0  202008.0  团队D  201909     91  \n",
       "4    57    男    是  会员赠送3个月  201909.0  202008.0  团队D  201909     99  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pyecharts\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = pd.read_excel(\"./运营商数据.xlsx\") #数据做了脱敏处理,请大家放心使用\n",
    "data.head()\n",
    "#特别注意:外呼分钟数单位为秒  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 为何活动这3个月的完成情况越来越差？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 重点字段解读：\n",
    "    - 是否参与活动：NAN表示没有参与活动，非空则表示参与活动\n",
    "    - 外呼时间：在活动月份期间对用户的呼叫时间/月份（9、10和11这三个月）\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 外呼渗透率：办理用户数 / 外呼用户数\n",
    "    - 办理用户数：活动月中,每个月参与此项活动的用户数量。\n",
    "        - 基于外呼时间进行分组然后对“是否参与活动”字段进行计数统计。\n",
    "    - 外呼用户数：活动月中,每个月外呼用户的数量。\n",
    "        - 基于外呼时间进行分组然后对“用户号码”字段进行计数统计。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户号码</th>\n",
       "      <th>是否参与活动</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1937</td>\n",
       "      <td>1050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7399</td>\n",
       "      <td>1864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>7162</td>\n",
       "      <td>1009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        用户号码  是否参与活动\n",
       "外呼时间                \n",
       "201909  1937    1050\n",
       "201910  7399    1864\n",
       "201911  7162    1009"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ret = data.groupby(data['外呼时间'])[['用户号码','是否参与活动']].count()\n",
    "ret"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼用户数</th>\n",
       "      <th>办理用户数</th>\n",
       "      <th>外呼渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1937</td>\n",
       "      <td>1050</td>\n",
       "      <td>54.21%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7399</td>\n",
       "      <td>1864</td>\n",
       "      <td>25.19%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>7162</td>\n",
       "      <td>1009</td>\n",
       "      <td>14.09%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        外呼用户数  办理用户数   外呼渗透率\n",
       "外呼时间                        \n",
       "201909   1937   1050  54.21%\n",
       "201910   7399   1864  25.19%\n",
       "201911   7162   1009  14.09%"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ret.columns = ['外呼用户数','办理用户数']\n",
    "ret['外呼渗透率'] = (ret['办理用户数'] / ret['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "ret"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，随着活动的进行，外呼渗透率情况越来越差。从外呼流程来看，当外呼员拨号到用户决定办理，需要经过以下几个阶段：\n",
    "\n",
    "- **外呼 => 用户应答 => 活动宣传 => 用户参与活动**\n",
    "\n",
    "而这其中的任意一个环节的转换都有可能带来渗透率的下降。例如：\n",
    "\n",
    "- 拨号但无人应答\n",
    "- 用户接听但发现是活动推广急于挂断\n",
    "- ……\n",
    "\n",
    "外呼团队的**工作质量**一部分影响了活动的宣传效果，而另一方面，**用户本身**的特征也决定了他们对活动的热情。下面分别从这两个维度展开分析。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 外呼质量方面分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "首先，在接通电话的用户中，**可以定义接通时间<20秒的为无效接通**，因为在短短20秒的时间内，外呼员可能无法将活动信息明确传达。因此可以定义如下指标，查看外呼团队的外呼质量：\n",
    "\n",
    "- **外呼接通率**：接通用户数/外呼用户数；\n",
    "- **接通渗透率**：办理用户数/接通用户数；\n",
    "- **有效接通率**：有效接通数/外呼用户数；>=20秒\n",
    "- **有效接通渗透率**：办理用户数/有效接通数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 计算**外呼接通率：**接通用户数/外呼用户数\n",
    "    - 接通用户数：活动月中，每个月\"外呼分钟数\"不为0的用户数量.\n",
    "        - 基于“外呼时间”进行分组然后对“外呼分钟数”不为0的用户进行计数统计。\n",
    "    - 外呼用户数：活动月中,每个月外呼用户的数量。\n",
    "        - 基于“外呼时间”进行分组然后对“用户号码”字段进行计数统计。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>接通用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>3023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        接通用户数\n",
       "外呼时间         \n",
       "201909   1936\n",
       "201910   7384\n",
       "201911   3023"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算  接通用户数：\n",
    "    #np.count_nonzero:统计数组中不为0的元素个数\n",
    "df1 = data.pivot_table(index='外呼时间',values='外呼分钟数',aggfunc=np.count_nonzero)\n",
    "df1.columns = ['接通用户数']\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>7162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        外呼用户数\n",
       "外呼时间         \n",
       "201909   1937\n",
       "201910   7399\n",
       "201911   7162"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算  外呼用户数：\n",
    "s2 = data.groupby(by='外呼时间')['用户号码'].count()\n",
    "df2 = pd.DataFrame(data=s2)\n",
    "df2.columns = ['外呼用户数']\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼接通率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>99.95%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>99.80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>42.21%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         外呼接通率\n",
       "外呼时间          \n",
       "201909  99.95%\n",
       "201910  99.80%\n",
       "201911  42.21%"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算  外呼接通率 ==> 接通用户数/外呼用户数\n",
    "s3 = (df1['接通用户数'] / df2['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "df3 = pd.DataFrame(data=s3)\n",
    "df3.columns = ['外呼接通率']\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 计算**接通渗透率：**办理用户数/接通用户数；\n",
    "    - 办理用户数：活动月内，每个月办理活动的用户数量\n",
    "    - 接通用户数：活动月内，每个月\"外呼分钟数\"不为0的用户数量."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>办理用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>1864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>1009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        办理用户数\n",
       "外呼时间         \n",
       "201909   1050\n",
       "201910   1864\n",
       "201911   1009"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#办理用户数\n",
    "s4 = data.groupby(by='外呼时间')['是否参与活动'].count()\n",
    "df4 = pd.DataFrame(data=s4)\n",
    "df4.columns = ['办理用户数']\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>接通用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>3023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        接通用户数\n",
       "外呼时间         \n",
       "201909   1936\n",
       "201910   7384\n",
       "201911   3023"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#接通用户数\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>接通渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>54.24%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>25.24%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>33.38%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         接通渗透率\n",
       "外呼时间          \n",
       "201909  54.24%\n",
       "201910  25.24%\n",
       "201911  33.38%"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#接通渗透率\n",
    "s5 = (df4['办理用户数'] / df1['接通用户数']).map(lambda x:format(x,'.2%'))\n",
    "df5 = pd.DataFrame(data=s5)\n",
    "df5.columns = ['接通渗透率']\n",
    "df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 计算**有效接通率：**有效接通数/外呼用户数；>=20秒\n",
    "    - 有效接通数：活动月中,每个月接通时间>=20秒的用户数量\n",
    "    - 外呼用户数：活动月中,每个月外呼用户的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户号码</th>\n",
       "      <th>用户套餐月租</th>\n",
       "      <th>入网时间</th>\n",
       "      <th>近6个月平均话费</th>\n",
       "      <th>近6个月平均使用流量</th>\n",
       "      <th>近6个月平均使用语音</th>\n",
       "      <th>优惠名称</th>\n",
       "      <th>号码品牌</th>\n",
       "      <th>用户年龄</th>\n",
       "      <th>用户性别</th>\n",
       "      <th>是否订购</th>\n",
       "      <th>是否参与活动</th>\n",
       "      <th>活动开始时间</th>\n",
       "      <th>活动结束时间</th>\n",
       "      <th>外呼团队</th>\n",
       "      <th>外呼时间</th>\n",
       "      <th>外呼分钟数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>20020209</td>\n",
       "      <td>146.2050</td>\n",
       "      <td>9090.910500</td>\n",
       "      <td>398.3167</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>55</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201911</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>20060424</td>\n",
       "      <td>50.0000</td>\n",
       "      <td>3980.592767</td>\n",
       "      <td>86.9000</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>51</td>\n",
       "      <td>男</td>\n",
       "      <td>否</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201909</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>20111206</td>\n",
       "      <td>67.1125</td>\n",
       "      <td>1706.841767</td>\n",
       "      <td>453.0833</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>36</td>\n",
       "      <td>女</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>56</td>\n",
       "      <td>20120412</td>\n",
       "      <td>99.0000</td>\n",
       "      <td>2872.303067</td>\n",
       "      <td>41.3500</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>35</td>\n",
       "      <td>女</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>88</td>\n",
       "      <td>20150503</td>\n",
       "      <td>88.0000</td>\n",
       "      <td>28222.901100</td>\n",
       "      <td>326.3500</td>\n",
       "      <td>送3个月会员</td>\n",
       "      <td>4G</td>\n",
       "      <td>57</td>\n",
       "      <td>男</td>\n",
       "      <td>是</td>\n",
       "      <td>会员赠送3个月</td>\n",
       "      <td>201909.0</td>\n",
       "      <td>202008.0</td>\n",
       "      <td>团队D</td>\n",
       "      <td>201909</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   用户号码  用户套餐月租      入网时间  近6个月平均话费    近6个月平均使用流量  近6个月平均使用语音    优惠名称 号码品牌  \\\n",
       "0     1      56  20020209  146.2050   9090.910500    398.3167  送3个月会员   4G   \n",
       "1     2      50  20060424   50.0000   3980.592767     86.9000  送3个月会员   4G   \n",
       "2     3      50  20111206   67.1125   1706.841767    453.0833  送3个月会员   4G   \n",
       "3     4      56  20120412   99.0000   2872.303067     41.3500  送3个月会员   4G   \n",
       "4     5      88  20150503   88.0000  28222.901100    326.3500  送3个月会员   4G   \n",
       "\n",
       "   用户年龄 用户性别 是否订购   是否参与活动    活动开始时间    活动结束时间 外呼团队    外呼时间  外呼分钟数  \n",
       "0    55    男    否      NaN       NaN       NaN  NaN  201911     91  \n",
       "1    51    男    否      NaN       NaN       NaN  NaN  201909     28  \n",
       "2    36    女    是  会员赠送3个月  201909.0  202008.0  团队D  201909    128  \n",
       "3    35    女    是  会员赠送3个月  201909.0  202008.0  团队D  201909     91  \n",
       "4    57    男    是  会员赠送3个月  201909.0  202008.0  团队D  201909     99  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex = data['外呼分钟数'] >= 20\n",
    "ret = data.loc[ex]#有效接通的行数据\n",
    "ret.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>有效接通数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>5932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>2523</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        有效接通数\n",
       "外呼时间         \n",
       "201909   1750\n",
       "201910   5932\n",
       "201911   2523"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#有效接通数\n",
    "s6 = ret.groupby(by='外呼时间')['用户号码'].count()\n",
    "df6 = pd.DataFrame(data=s6)\n",
    "df6.columns = ['有效接通数']\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>7162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        外呼用户数\n",
       "外呼时间         \n",
       "201909   1937\n",
       "201910   7399\n",
       "201911   7162"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#外呼用户数\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>有效接通率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>90.35%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>80.17%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>35.23%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         有效接通率\n",
       "外呼时间          \n",
       "201909  90.35%\n",
       "201910  80.17%\n",
       "201911  35.23%"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#有效接通率:有效接通数/外呼用户数\n",
    "s7 = (df6['有效接通数'] / df2['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "df7 = pd.DataFrame(data=s7)\n",
    "df7.columns = ['有效接通率']\n",
    "df7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 计算**有效接通渗透率**：办理用户数/有效接通用户数\n",
    "    - 办理用户数：活动月内，每个月办理活动的用户数量\n",
    "    - 有效接通数：活动月中,每个月接通时间>=20秒的用户数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>有效接通渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>60.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>31.42%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>39.99%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       有效接通渗透率\n",
       "外呼时间          \n",
       "201909  60.00%\n",
       "201910  31.42%\n",
       "201911  39.99%"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#有效接通渗透率\n",
    "s8 = (df4['办理用户数'] / df6['有效接通数']).map(lambda x:format(x,'.2%'))\n",
    "df8 = pd.DataFrame(data=s8)\n",
    "df8.columns = ['有效接通渗透率']\n",
    "df8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 汇总相关计算的指标数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>接通用户数</th>\n",
       "      <th>外呼用户数</th>\n",
       "      <th>外呼接通率</th>\n",
       "      <th>办理用户数</th>\n",
       "      <th>接通渗透率</th>\n",
       "      <th>有效接通数</th>\n",
       "      <th>有效接通率</th>\n",
       "      <th>有效接通渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1936</td>\n",
       "      <td>1937</td>\n",
       "      <td>99.95%</td>\n",
       "      <td>1050</td>\n",
       "      <td>54.24%</td>\n",
       "      <td>1750</td>\n",
       "      <td>90.35%</td>\n",
       "      <td>60.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7384</td>\n",
       "      <td>7399</td>\n",
       "      <td>99.80%</td>\n",
       "      <td>1864</td>\n",
       "      <td>25.24%</td>\n",
       "      <td>5932</td>\n",
       "      <td>80.17%</td>\n",
       "      <td>31.42%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>3023</td>\n",
       "      <td>7162</td>\n",
       "      <td>42.21%</td>\n",
       "      <td>1009</td>\n",
       "      <td>33.38%</td>\n",
       "      <td>2523</td>\n",
       "      <td>35.23%</td>\n",
       "      <td>39.99%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        接通用户数  外呼用户数   外呼接通率  办理用户数   接通渗透率  有效接通数   有效接通率 有效接通渗透率\n",
       "外呼时间                                                              \n",
       "201909   1936   1937  99.95%   1050  54.24%   1750  90.35%  60.00%\n",
       "201910   7384   7399  99.80%   1864  25.24%   5932  80.17%  31.42%\n",
       "201911   3023   7162  42.21%   1009  33.38%   2523  35.23%  39.99%"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_ret_df = pd.concat([df1,df2,df3,df4,df5,df6,df7,df8],axis=1)\n",
    "all_ret_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 基于漏斗图展示活动月期间每月的外呼用户数、接通用户数、有效接通数和活动办理数之间的转换率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"1e2a3bc267cd4dd783389facae604b47\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_1e2a3bc267cd4dd783389facae604b47 = echarts.init(\n",
       "                    document.getElementById('1e2a3bc267cd4dd783389facae604b47'), 'white', {renderer: 'canvas'});\n",
       "                var option_1e2a3bc267cd4dd783389facae604b47 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"funnel\",\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 1937\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 1936\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                    \"value\": 1750\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\",\n",
       "                    \"value\": 1050\n",
       "                }\n",
       "            ],\n",
       "            \"sort\": \"descending\",\n",
       "            \"gap\": 0,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\": true,\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"top\": \"5%\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"9\\u6708\\u6f0f\\u6597\\u56fe\",\n",
       "            \"left\": \"center\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_1e2a3bc267cd4dd783389facae604b47.setOption(option_1e2a3bc267cd4dd783389facae604b47);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7f9f915541f0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyecharts.charts import Funnel\n",
    "from pyecharts import options as opts\n",
    "\n",
    "#9月份\n",
    "funnel1 = (Funnel()\n",
    "        .add(\"\",[['外呼用户数', 1937], ['接通用户数', 1936], ['有效接通数', 1750], ['活动办理数', 1050]])\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"9月漏斗图\",pos_left=\"center\"),\n",
    "        legend_opts=opts.LegendOpts(pos_top=\"5%\")))\n",
    "#10月份\n",
    "funnel2 = (Funnel()\n",
    "        .add(\"\",[['外呼用户数', 7399], ['接通用户数', 7384], ['有效接通数', 5932], ['活动办理数', 1864]])\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"10月漏斗图\",pos_left=\"center\"),\n",
    "        legend_opts=opts.LegendOpts(pos_top=\"5%\")))\n",
    "#11月份\n",
    "funnel3 = (Funnel()\n",
    "        .add(\"\",[['外呼用户数', 7162], ['接通用户数', 3023], ['有效接通数', 2523], ['活动办理数', 1009]])\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"11月漏斗图\",pos_left=\"center\"),\n",
    "        legend_opts=opts.LegendOpts(pos_top=\"5%\")))\n",
    "funnel1.render_notebook()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"a04ec92358c74d56be5dfd8f8352274a\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_a04ec92358c74d56be5dfd8f8352274a = echarts.init(\n",
       "                    document.getElementById('a04ec92358c74d56be5dfd8f8352274a'), 'white', {renderer: 'canvas'});\n",
       "                var option_a04ec92358c74d56be5dfd8f8352274a = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"funnel\",\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 7399\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 7384\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                    \"value\": 5932\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\",\n",
       "                    \"value\": 1864\n",
       "                }\n",
       "            ],\n",
       "            \"sort\": \"descending\",\n",
       "            \"gap\": 0,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\": true,\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"top\": \"5%\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"10\\u6708\\u6f0f\\u6597\\u56fe\",\n",
       "            \"left\": \"center\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_a04ec92358c74d56be5dfd8f8352274a.setOption(option_a04ec92358c74d56be5dfd8f8352274a);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7f9f600740d0>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "funnel2.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"e9ea371c22ee4ca08d353fc2544909bd\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_e9ea371c22ee4ca08d353fc2544909bd = echarts.init(\n",
       "                    document.getElementById('e9ea371c22ee4ca08d353fc2544909bd'), 'white', {renderer: 'canvas'});\n",
       "                var option_e9ea371c22ee4ca08d353fc2544909bd = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"funnel\",\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 7162\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                    \"value\": 3023\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                    \"value\": 2523\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\",\n",
       "                    \"value\": 1009\n",
       "                }\n",
       "            ],\n",
       "            \"sort\": \"descending\",\n",
       "            \"gap\": 0,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\",\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\",\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u5916\\u547c\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u63a5\\u901a\\u7528\\u6237\\u6570\": true,\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6570\": true,\n",
       "                \"\\u6d3b\\u52a8\\u529e\\u7406\\u6570\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"top\": \"5%\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"11\\u6708\\u6f0f\\u6597\\u56fe\",\n",
       "            \"left\": \"center\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_e9ea371c22ee4ca08d353fc2544909bd.setOption(option_e9ea371c22ee4ca08d353fc2544909bd);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7f9f40038e20>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "funnel3.render_notebook()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从漏斗图中可以大致看出9月这四个环节的转换/渗透率趋于正常，10月、11月渗透率下降的原因不尽相同：10月渗透率下降主要在**有效接通**环节，而11月渗透率下降主要在**用户接通**环节。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "基于柱状图展示下带率指标的情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"c4163a2b7655450385a4299323799d5d\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_c4163a2b7655450385a4299323799d5d = echarts.init(\n",
       "                    document.getElementById('c4163a2b7655450385a4299323799d5d'), 'white', {renderer: 'canvas'});\n",
       "                var option_c4163a2b7655450385a4299323799d5d = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u5916\\u547c\\u63a5\\u901a\\u7387\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                99.95,\n",
       "                99.8,\n",
       "                42.21\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"0%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u6709\\u6548\\u63a5\\u901a\\u7387\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                90.35,\n",
       "                80.17,\n",
       "                35.23\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"0%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u6709\\u6548\\u63a5\\u901a\\u6e17\\u900f\\u7387\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                60.0,\n",
       "                31.42,\n",
       "                39.99\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"0%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u5916\\u547c\\u63a5\\u901a\\u7387\",\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u7387\",\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6e17\\u900f\\u7387\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u5916\\u547c\\u63a5\\u901a\\u7387\": true,\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u7387\": true,\n",
       "                \"\\u6709\\u6548\\u63a5\\u901a\\u6e17\\u900f\\u7387\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"top\": \"5%\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"formatter\": \"{a}: {c}\",\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"2019\\u5e749\\u6708\",\n",
       "                \"2019\\u5e7410\\u6708\",\n",
       "                \"2019\\u5e7411\\u6708\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8,\n",
       "                \"formatter\": \"{value} %\"\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"max\": 100,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"value\",\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"position\": \"right\",\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u5916\\u547c\\u63a5\\u901a\\u53ca\\u6e17\\u900f\\u60c5\\u51b5\",\n",
       "            \"left\": \"center\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_c4163a2b7655450385a4299323799d5d.setOption(option_c4163a2b7655450385a4299323799d5d);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7f9f60086670>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyecharts.charts import Line\n",
    "from pyecharts.charts import Bar\n",
    "\n",
    "bar = Bar()\n",
    "bar.add_xaxis(['2019年9月','2019年10月','2019年11月'])\n",
    "bar.add_yaxis('外呼接通率', df3['外呼接通率'].map(lambda x: float(x.strip('%'))).tolist(),gap=\"0%\")\n",
    "bar.add_yaxis('有效接通率', df7['有效接通率'].map(lambda x: float(x.strip('%'))).tolist(),gap=\"0%\")\n",
    "bar.add_yaxis('有效接通渗透率', df8['有效接通渗透率'].map(lambda x: float(x.strip('%'))).tolist(),gap=\"0%\")\n",
    "bar.set_global_opts(\n",
    "                    title_opts=opts.TitleOpts(title=\"外呼接通及渗透情况\",pos_left=\"center\"),\n",
    "                    legend_opts=opts.LegendOpts(pos_top=\"5%\"),\n",
    "                    yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter=\"{value} %\"),max_=100),\n",
    "                    tooltip_opts=opts.TooltipOpts(formatter=\"{a}: {c}\")\n",
    "                    )\n",
    "bar.extend_axis(yaxis=opts.AxisOpts(type_=\"value\", position=\"right\",))\n",
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从图中可以看到，10月、11月相对于9月份的**有效接通渗透率下滑明显**，而11月的**外呼接通率、有效接通率**更是下滑严重。由此可见：\n",
    "\n",
    "- 10月、11月的外呼通话质量有所下降，可能是由于外呼用户数量大幅增长导致的质量有所松懈；\n",
    "- 11月外呼团队的外呼策略可能出现了问题，例如选取了不合适的时间段进行外呼等；\n",
    "\n",
    "除了以上因素之外，通话时长也是衡量外呼质量的一个重要因素。计算3个月接通外呼的平均通话时长如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼分钟数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   外呼分钟数\n",
       "0     91\n",
       "1     28\n",
       "2    128\n",
       "3     91\n",
       "4     99"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#获取外呼分钟数不为0的行数据\n",
    "ex = data['外呼分钟数'] != 0\n",
    "df = data.loc[ex][['外呼分钟数']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>平均外呼接通时长</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>76.302169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>61.560672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>62.278200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         平均外呼接通时长\n",
       "外呼时间             \n",
       "201909  76.302169\n",
       "201910  61.560672\n",
       "201911  62.278200"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#基于外呼时间进行分组计算每个月的平均外呼时长\n",
    "time = df.groupby(by=data['外呼时间']).mean()\n",
    "time.columns = ['平均外呼接通时长']\n",
    "time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>平均外呼接通时长</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>76.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>61.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>62.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       平均外呼接通时长\n",
       "外呼时间           \n",
       "201909    76.30\n",
       "201910    61.56\n",
       "201911    62.28"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将每个月的平均外呼时长保留两位小数进行显示\n",
    "time['平均外呼接通时长'] = time['平均外呼接通时长'].map(lambda x:format(x,'.2f'))\n",
    "time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，10月、11月的平均通话时长均较9月下滑。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 继续对通话时长细分，统计每个月各通话时长的分布情况，并计算相应的渗透率:\n",
    "    - 渗透率:活动月中，每月不同区间的外呼分钟数对应的参与活动的用户数量 / 每月的总用户数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对外呼时间进行不同区间制定，作为新的一列进行保存\n",
    "data['bin'] = pd.cut(data['外呼分钟数'],bins=[-1,0,20,40,60,1000])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>5</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>10</td>\n",
       "      <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>94</td>\n",
       "      <td>284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>940</td>\n",
       "      <td>1253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>6</td>\n",
       "      <td>1452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>24</td>\n",
       "      <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>290</td>\n",
       "      <td>1267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>1543</td>\n",
       "      <td>3165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>4139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>0</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>21</td>\n",
       "      <td>628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>251</td>\n",
       "      <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>736</td>\n",
       "      <td>1265</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   参与活动数   用户数\n",
       "外呼时间   bin                    \n",
       "201909 (-1, 0]         1     1\n",
       "       (0, 20]         5   186\n",
       "       (20, 40]       10   213\n",
       "       (40, 60]       94   284\n",
       "       (60, 1000]    940  1253\n",
       "201910 (-1, 0]         1    15\n",
       "       (0, 20]         6  1452\n",
       "       (20, 40]       24  1500\n",
       "       (40, 60]      290  1267\n",
       "       (60, 1000]   1543  3165\n",
       "201911 (-1, 0]         1  4139\n",
       "       (0, 20]         0   500\n",
       "       (20, 40]       21   628\n",
       "       (40, 60]      251   630\n",
       "       (60, 1000]    736  1265"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算每个月的不同区间通话时间对应的用户数量和参与活动的用户数量\n",
    "bin_p = data.pivot_table(index=['外呼时间','bin'],values=['用户号码','是否参与活动'],aggfunc=['count'])\n",
    "bin_p = bin_p['count']\n",
    "\n",
    "bin_p.columns = ['参与活动数','用户数']\n",
    "bin_p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#计算渗透率\n",
    "bin_p['渗透率'] = (bin_p['参与活动数']/bin_p['用户数']).map(lambda x:format(x,'.2f'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>5</td>\n",
       "      <td>186</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>10</td>\n",
       "      <td>213</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>94</td>\n",
       "      <td>284</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>940</td>\n",
       "      <td>1253</td>\n",
       "      <td>0.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>6</td>\n",
       "      <td>1452</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>24</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>290</td>\n",
       "      <td>1267</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>1543</td>\n",
       "      <td>3165</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 0]</th>\n",
       "      <td>1</td>\n",
       "      <td>4139</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>0</td>\n",
       "      <td>500</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>21</td>\n",
       "      <td>628</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>251</td>\n",
       "      <td>630</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(60, 1000]</th>\n",
       "      <td>736</td>\n",
       "      <td>1265</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   参与活动数   用户数   渗透率\n",
       "外呼时间   bin                          \n",
       "201909 (-1, 0]         1     1  1.00\n",
       "       (0, 20]         5   186  0.03\n",
       "       (20, 40]       10   213  0.05\n",
       "       (40, 60]       94   284  0.33\n",
       "       (60, 1000]    940  1253  0.75\n",
       "201910 (-1, 0]         1    15  0.07\n",
       "       (0, 20]         6  1452  0.00\n",
       "       (20, 40]       24  1500  0.02\n",
       "       (40, 60]      290  1267  0.23\n",
       "       (60, 1000]   1543  3165  0.49\n",
       "201911 (-1, 0]         1  4139  0.00\n",
       "       (0, 20]         0   500  0.00\n",
       "       (20, 40]       21   628  0.03\n",
       "       (40, 60]      251   630  0.40\n",
       "       (60, 1000]    736  1265  0.58"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bin_p"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在用户接听电话的情况下（外呼分钟数>0），外呼渗透率随着外呼时间的延长而有所提高。从各个时段的外呼渗透情况来看，在**有效接通**（接通时长>20秒）的情况下，10月各个时段的外呼渗透率均小于9月与11月。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>201909</th>\n",
       "      <th>201910</th>\n",
       "      <th>201911</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>&gt;60</th>\n",
       "      <td>1253</td>\n",
       "      <td>3165</td>\n",
       "      <td>1265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(40, 60]</th>\n",
       "      <td>284</td>\n",
       "      <td>1267</td>\n",
       "      <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 40]</th>\n",
       "      <td>213</td>\n",
       "      <td>1500</td>\n",
       "      <td>628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(0, 20]</th>\n",
       "      <td>186</td>\n",
       "      <td>1452</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>未接通</th>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>4139</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          201909  201910  201911\n",
       ">60         1253    3165    1265\n",
       "(40, 60]     284    1267     630\n",
       "(20, 40]     213    1500     628\n",
       "(0, 20]      186    1452     500\n",
       "未接通            1      15    4139"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看每个月 不同区间的外呼分钟数 对应的用户数量，保存到一个新的表格中\n",
    "count = pd.DataFrame(columns = ['201909','201910','201911'])\n",
    "\n",
    "for month in range(201909,201912):\n",
    "    #对不同月份的行数据的外呼分钟数进行分箱操作\n",
    "    bins = pd.cut(data.loc[data.外呼时间 == month]['外呼分钟数'],\n",
    "                  bins=[-1,0,20,40,60,1000])\n",
    "    \n",
    "    #统计每个箱子表示指定区间外呼时间对应的用户数量\n",
    "    count[str(month)] = pd.Series(bins.value_counts().sort_index(ascending = False))\n",
    "    month += 1\n",
    "\n",
    "#将表格的行索引使用外呼时间的不同区间表示\n",
    "count.index = ['>60', '(40, 60]', '(20, 40]', '(0, 20]', '未接通']\n",
    "\n",
    "count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "而11月最大的问题还是出现了过半的未接通用户，这反映了11月外呼策略上出了问题，比如选择了不合适的时间段进行外呼。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 总结：\n",
    "\n",
    "- 10月、11月的外呼通话质量有所下降，可能是由于外呼用户数量大幅增长导致的质量有所松懈；11月外呼团队的外呼策略可能出现了问题，例如选取了不合适的时间段进行外呼等；\n",
    "\n",
    "- 在用户接听电话的情况下（外呼分钟数>0），外呼渗透率随着外呼时间的延长而有所提高。从各个时段的外呼渗透情况来看，在有效接通（接通时长>20秒）的情况下，10月各个时段的外呼渗透率均小于9月与11月。\n",
    "- 11月最大的问题还是出现了过半的未接通用户，这反映了11月外呼策略上出了问题，比如选择了不合适的时间段进行外呼。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户特征方面分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 用户性别\n",
    "首先，观察用户性别是否对其参与活动的热情产生影响。注意送的是视频会员,这和性别可能真的有关系"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 计算针对不同性别的如下指标：\n",
    "    - **外呼接通率**：接通用户数/外呼用户数；\n",
    "    - **接通渗透率**：活动办理数/接通用户数；\n",
    "    - **有效接通率**：有效接通数/外呼用户数；>=20秒\n",
    "    - **有效接通渗透率**：活动办理数/有效接通数\n",
    "    - **渗透率**：活动办理数/外呼用户数\n",
    "        - 外呼用户数：\n",
    "            - 不同性别对应的用户数量\n",
    "        - 接通用户数：\n",
    "            - 不同性别对应外呼分钟数不为0的用户数量\n",
    "        - 有效接通数：\n",
    "            - 不同性别的外呼分钟数>=20s的用户数量\n",
    "        - 活动办理数：\n",
    "            - 不同性别中，参与活动的用户数量\n",
    "            - 在“是否参与活动”列中，NAN表示没有参与活动，非空表示参与活动\n",
    "        \n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户性别\n",
       "女     5800\n",
       "男    10698\n",
       "Name: 用户号码, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#外呼用户数：不同性别对应的用户数量\n",
    "s1 = data.groupby(by='用户性别')['用户号码'].count()\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户性别\n",
       "女    3955\n",
       "男    8388\n",
       "Name: 外呼分钟数, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#接通用户数：不同性别对应外呼分钟数不为0的用户数量\n",
    "def func(x):\n",
    "    return np.count_nonzero(x)\n",
    "s2 = data.groupby(by='用户性别')['外呼分钟数'].apply(func)\n",
    "s2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户性别\n",
       "女    3221\n",
       "男    6984\n",
       "Name: 用户号码, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#有效接通数：不同性别的外呼分钟数>=20s的用户数量\n",
    "s3 = data.loc[data['外呼分钟数']>=20].groupby(data['用户性别'])['用户号码'].count()\n",
    "s3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户性别\n",
       "女    1223\n",
       "男    2700\n",
       "Name: 是否参与活动, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#活动办理数：不同性别中，参与活动的用户数量\n",
    "s4 = data.groupby(by='用户性别')['是否参与活动'].count()\n",
    "s4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼用户数</th>\n",
       "      <th>接通用户数</th>\n",
       "      <th>有效接通数</th>\n",
       "      <th>活动办理数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户性别</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>女</th>\n",
       "      <td>5800</td>\n",
       "      <td>3955</td>\n",
       "      <td>3221</td>\n",
       "      <td>1223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>男</th>\n",
       "      <td>10698</td>\n",
       "      <td>8388</td>\n",
       "      <td>6984</td>\n",
       "      <td>2700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      外呼用户数  接通用户数  有效接通数  活动办理数\n",
       "用户性别                            \n",
       "女      5800   3955   3221   1223\n",
       "男     10698   8388   6984   2700"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([s1,s2,s3,s4],index=['外呼用户数','接通用户数','有效接通数','活动办理数']).T\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>外呼用户数</th>\n",
       "      <th>接通用户数</th>\n",
       "      <th>有效接通数</th>\n",
       "      <th>活动办理数</th>\n",
       "      <th>外呼接通率</th>\n",
       "      <th>接通渗透率</th>\n",
       "      <th>有效接通率</th>\n",
       "      <th>有效接通渗透率</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户性别</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>女</th>\n",
       "      <td>5800</td>\n",
       "      <td>3955</td>\n",
       "      <td>3221</td>\n",
       "      <td>1223</td>\n",
       "      <td>68.19%</td>\n",
       "      <td>30.92%</td>\n",
       "      <td>55.53%</td>\n",
       "      <td>37.97%</td>\n",
       "      <td>21.09%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>男</th>\n",
       "      <td>10698</td>\n",
       "      <td>8388</td>\n",
       "      <td>6984</td>\n",
       "      <td>2700</td>\n",
       "      <td>78.41%</td>\n",
       "      <td>32.19%</td>\n",
       "      <td>65.28%</td>\n",
       "      <td>38.66%</td>\n",
       "      <td>25.24%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      外呼用户数  接通用户数  有效接通数  活动办理数   外呼接通率   接通渗透率   有效接通率 有效接通渗透率     渗透率\n",
       "用户性别                                                                    \n",
       "女      5800   3955   3221   1223  68.19%  30.92%  55.53%  37.97%  21.09%\n",
       "男     10698   8388   6984   2700  78.41%  32.19%  65.28%  38.66%  25.24%"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#外呼接通率：接通用户数/外呼用户数\n",
    "df['外呼接通率'] = (df['接通用户数'] / df['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "#接通渗透率：活动办理数/接通用户数；\n",
    "df['接通渗透率'] = (df['活动办理数'] / df['接通用户数']).map(lambda x:format(x,'.2%'))\n",
    "#有效接通率：有效接通数/外呼用户数；\n",
    "df['有效接通率'] = (df['有效接通数'] / df['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "#有效接通渗透率：活动办理数/有效接通数\n",
    "df['有效接通渗透率'] = (df['活动办理数'] / df['有效接通数']).map(lambda x:format(x,'.2%'))\n",
    "#渗透率：活动办理数/外呼用户数\n",
    "df['渗透率'] = (df['活动办理数'] / df['外呼用户数']).map(lambda x:format(x,'.2%'))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到整体的数据，是男性的渗透率、接通率、有效接通率以及接通渗透率等指标均高于女性，但是没有高太多。why？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "下面观察这活动的3个月内不同性别外呼用户数的比例(针对不同月份不同性别的查看)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户性别</th>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">女</th>\n",
       "      <th>201909</th>\n",
       "      <td>470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>2483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>2847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">男</th>\n",
       "      <th>201909</th>\n",
       "      <td>1467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>4916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>4315</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              用户数\n",
       "用户性别 外呼时间        \n",
       "女    201909   470\n",
       "     201910  2483\n",
       "     201911  2847\n",
       "男    201909  1467\n",
       "     201910  4916\n",
       "     201911  4315"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#不同性别在外呼时间（月份）的用户数量\n",
    "ret = data.groupby(['用户性别','外呼时间'])['用户号码'].count()\n",
    "df1 = pd.DataFrame(ret)\n",
    "df1.columns = ['用户数']\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户总数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>201909</th>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>7399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>7162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        用户总数\n",
       "外呼时间        \n",
       "201909  1937\n",
       "201910  7399\n",
       "201911  7162"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#不同月份的总用户数\n",
    "df2 = pd.DataFrame(data.groupby(['外呼时间'])['用户号码'].count())\n",
    "df2.columns = ['用户总数']\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户数</th>\n",
       "      <th>用户总数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户性别</th>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">女</th>\n",
       "      <th>201909</th>\n",
       "      <td>470</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>2483</td>\n",
       "      <td>7399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>2847</td>\n",
       "      <td>7162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">男</th>\n",
       "      <th>201909</th>\n",
       "      <td>1467</td>\n",
       "      <td>1937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>4916</td>\n",
       "      <td>7399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>4315</td>\n",
       "      <td>7162</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              用户数  用户总数\n",
       "用户性别 外呼时间              \n",
       "女    201909   470  1937\n",
       "     201910  2483  7399\n",
       "     201911  2847  7162\n",
       "男    201909  1467  1937\n",
       "     201910  4916  7399\n",
       "     201911  4315  7162"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#数据合并\n",
    "df = df1.join(df2,on='外呼时间')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>用户数</th>\n",
       "      <th>用户总数</th>\n",
       "      <th>占比</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户性别</th>\n",
       "      <th>外呼时间</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">女</th>\n",
       "      <th>201909</th>\n",
       "      <td>470</td>\n",
       "      <td>1937</td>\n",
       "      <td>0.242643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>2483</td>\n",
       "      <td>7399</td>\n",
       "      <td>0.335586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>2847</td>\n",
       "      <td>7162</td>\n",
       "      <td>0.397515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">男</th>\n",
       "      <th>201909</th>\n",
       "      <td>1467</td>\n",
       "      <td>1937</td>\n",
       "      <td>0.757357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201910</th>\n",
       "      <td>4916</td>\n",
       "      <td>7399</td>\n",
       "      <td>0.664414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201911</th>\n",
       "      <td>4315</td>\n",
       "      <td>7162</td>\n",
       "      <td>0.602485</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              用户数  用户总数        占比\n",
       "用户性别 外呼时间                        \n",
       "女    201909   470  1937  0.242643\n",
       "     201910  2483  7399  0.335586\n",
       "     201911  2847  7162  0.397515\n",
       "男    201909  1467  1937  0.757357\n",
       "     201910  4916  7399  0.664414\n",
       "     201911  4315  7162  0.602485"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算占比\n",
    "df['占比'] = df['用户数'] / df['用户总数']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，从9月到11月，外呼的女性占比显著提高，这也导致了外呼接通率、渗透率等指标的下降。因此性别对整体渗透率来讲没有造成太大影响。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户年龄\n",
    "查看在活动月中，不同年龄层次之间的渗透率情况如何？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15, 101)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看用户年龄的大致分布情况\n",
    "data['用户年龄'].min(),data['用户年龄'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#将用户年龄划分中几个区间进行分析\n",
    "data['bin'] = pd.cut(data['用户年龄'],bins=[14,18.5,25.5,35.5,50.5,80.5,101])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>271.0</td>\n",
       "      <td>421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>375.0</td>\n",
       "      <td>644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>280.0</td>\n",
       "      <td>620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>117.0</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">201910</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>233.0</td>\n",
       "      <td>525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>423.0</td>\n",
       "      <td>1171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>627.0</td>\n",
       "      <td>2406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>574.0</td>\n",
       "      <td>3211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(80.5, 101.0]</th>\n",
       "      <td>6.0</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>107.0</td>\n",
       "      <td>642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>265.0</td>\n",
       "      <td>1577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>371.0</td>\n",
       "      <td>2856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>266.0</td>\n",
       "      <td>2086</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      参与活动数   用户数\n",
       "外呼时间   bin                       \n",
       "201909 (14.0, 18.5]     7.0    11\n",
       "       (18.5, 25.5]   271.0   421\n",
       "       (25.5, 35.5]   375.0   644\n",
       "       (35.5, 50.5]   280.0   620\n",
       "       (50.5, 80.5]   117.0   241\n",
       "201910 (14.0, 18.5]     1.0     3\n",
       "       (18.5, 25.5]   233.0   525\n",
       "       (25.5, 35.5]   423.0  1171\n",
       "       (35.5, 50.5]   627.0  2406\n",
       "       (50.5, 80.5]   574.0  3211\n",
       "       (80.5, 101.0]    6.0    83\n",
       "201911 (14.0, 18.5]     0.0     1\n",
       "       (18.5, 25.5]   107.0   642\n",
       "       (25.5, 35.5]   265.0  1577\n",
       "       (35.5, 50.5]   371.0  2856\n",
       "       (50.5, 80.5]   266.0  2086"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#统计活动月中，每个月中不同用户年龄的参与活动数和用户总数\n",
    "bin_penetration = data.pivot_table(index=['外呼时间','bin'],values=['用户号码','是否参与活动'],aggfunc=['count'])['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>7.0</td>\n",
       "      <td>11</td>\n",
       "      <td>0.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>271.0</td>\n",
       "      <td>421</td>\n",
       "      <td>0.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>375.0</td>\n",
       "      <td>644</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>280.0</td>\n",
       "      <td>620</td>\n",
       "      <td>0.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>117.0</td>\n",
       "      <td>241</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">201910</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>233.0</td>\n",
       "      <td>525</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>423.0</td>\n",
       "      <td>1171</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>627.0</td>\n",
       "      <td>2406</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>574.0</td>\n",
       "      <td>3211</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(80.5, 101.0]</th>\n",
       "      <td>6.0</td>\n",
       "      <td>83</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(14.0, 18.5]</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(18.5, 25.5]</th>\n",
       "      <td>107.0</td>\n",
       "      <td>642</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(25.5, 35.5]</th>\n",
       "      <td>265.0</td>\n",
       "      <td>1577</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(35.5, 50.5]</th>\n",
       "      <td>371.0</td>\n",
       "      <td>2856</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50.5, 80.5]</th>\n",
       "      <td>266.0</td>\n",
       "      <td>2086</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      参与活动数   用户数   渗透率\n",
       "外呼时间   bin                             \n",
       "201909 (14.0, 18.5]     7.0    11  0.64\n",
       "       (18.5, 25.5]   271.0   421  0.64\n",
       "       (25.5, 35.5]   375.0   644  0.58\n",
       "       (35.5, 50.5]   280.0   620  0.45\n",
       "       (50.5, 80.5]   117.0   241  0.49\n",
       "201910 (14.0, 18.5]     1.0     3  0.33\n",
       "       (18.5, 25.5]   233.0   525  0.44\n",
       "       (25.5, 35.5]   423.0  1171  0.36\n",
       "       (35.5, 50.5]   627.0  2406  0.26\n",
       "       (50.5, 80.5]   574.0  3211  0.18\n",
       "       (80.5, 101.0]    6.0    83  0.07\n",
       "201911 (14.0, 18.5]     0.0     1  0.00\n",
       "       (18.5, 25.5]   107.0   642  0.17\n",
       "       (25.5, 35.5]   265.0  1577  0.17\n",
       "       (35.5, 50.5]   371.0  2856  0.13\n",
       "       (50.5, 80.5]   266.0  2086  0.13"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算其渗透率：参与活动数/用户数\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x:format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "35岁以下的较年轻用户渗透率明显较高。与9月对比，**10月、11月外呼用户侧重点明显偏移到了高龄用户**，进而导致了渗透率的下跌。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户套餐月租\n",
    "从用户套餐月租的分布情况来看不同月租段的用户渗透率变化情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#用户套餐分布区间制定\n",
    "data['bin'] = pd.cut(data['用户套餐月租'],bins=[-1,20,50,100,200,400])\n",
    "#计算不同套餐不同区间的参与活动数和总用户数\n",
    "bin_penetration = pd.pivot_table(data,index=['外呼时间','bin'],\n",
    "                                 values=['用户号码','是否参与活动'],\n",
    "                                 aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "#计算渗透率\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/\n",
    "                          bin_penetration['用户数']).map(lambda x:format(x,'.2f'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">201909</th>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>26.0</td>\n",
       "      <td>62</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>815.0</td>\n",
       "      <td>1504</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>209.0</td>\n",
       "      <td>371</td>\n",
       "      <td>0.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 20]</th>\n",
       "      <td>1238.0</td>\n",
       "      <td>5622</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>407.0</td>\n",
       "      <td>1316</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>202.0</td>\n",
       "      <td>424</td>\n",
       "      <td>0.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>16.0</td>\n",
       "      <td>36</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 400]</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 20]</th>\n",
       "      <td>423.0</td>\n",
       "      <td>4358</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>146.0</td>\n",
       "      <td>857</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>382.0</td>\n",
       "      <td>1724</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>58.0</td>\n",
       "      <td>223</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    参与活动数   用户数   渗透率\n",
       "外呼时间   bin                           \n",
       "201909 (20, 50]      26.0    62  0.42\n",
       "       (50, 100]    815.0  1504  0.54\n",
       "       (100, 200]   209.0   371  0.56\n",
       "201910 (-1, 20]    1238.0  5622  0.22\n",
       "       (20, 50]     407.0  1316  0.31\n",
       "       (50, 100]    202.0   424  0.48\n",
       "       (100, 200]    16.0    36  0.44\n",
       "       (200, 400]     1.0     1  1.00\n",
       "201911 (-1, 20]     423.0  4358  0.10\n",
       "       (20, 50]     146.0   857  0.17\n",
       "       (50, 100]    382.0  1724  0.22\n",
       "       (100, 200]    58.0   223  0.26"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，用户的套餐月租与渗透率大致呈正相关关系：总体来说，用户套餐月租越高，用户参与活动的意向越高。整体来看9月的渗透率好于10月和11月。**10月和11月外呼的月租套餐低于30元的用户显著高于9月**，而恰恰是这一部分用户带来了极低的渗透率，导致了整体渗透率的下降。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 入网时间\n",
    "从用户的入网时间（针对不同入网年份）分布情况来看不同入网时间段的用户渗透率变化情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#提取入网时间的年份\n",
    "def func(d):\n",
    "    return int(str(d)[0:4])\n",
    "data['years'] = data['入网时间'].map(func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "#制定入网时间的区间分布\n",
    "data['bin'] = pd.cut(data['years'],bins=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(1997.979, 2002.2]</th>\n",
       "      <td>19</td>\n",
       "      <td>52</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2002.2, 2006.4]</th>\n",
       "      <td>25</td>\n",
       "      <td>48</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2006.4, 2010.6]</th>\n",
       "      <td>72</td>\n",
       "      <td>146</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2010.6, 2014.8]</th>\n",
       "      <td>469</td>\n",
       "      <td>814</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2014.8, 2019.0]</th>\n",
       "      <td>465</td>\n",
       "      <td>877</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(1997.979, 2002.2]</th>\n",
       "      <td>240</td>\n",
       "      <td>878</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2002.2, 2006.4]</th>\n",
       "      <td>252</td>\n",
       "      <td>1486</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2006.4, 2010.6]</th>\n",
       "      <td>261</td>\n",
       "      <td>1386</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2010.6, 2014.8]</th>\n",
       "      <td>618</td>\n",
       "      <td>2258</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2014.8, 2019.0]</th>\n",
       "      <td>493</td>\n",
       "      <td>1391</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(1997.979, 2002.2]</th>\n",
       "      <td>85</td>\n",
       "      <td>445</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2002.2, 2006.4]</th>\n",
       "      <td>121</td>\n",
       "      <td>1007</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2006.4, 2010.6]</th>\n",
       "      <td>142</td>\n",
       "      <td>1181</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2010.6, 2014.8]</th>\n",
       "      <td>385</td>\n",
       "      <td>2711</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(2014.8, 2019.0]</th>\n",
       "      <td>276</td>\n",
       "      <td>1818</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           参与活动数   用户数   渗透率\n",
       "外呼时间   bin                                  \n",
       "201909 (1997.979, 2002.2]     19    52  0.37\n",
       "       (2002.2, 2006.4]       25    48  0.52\n",
       "       (2006.4, 2010.6]       72   146  0.49\n",
       "       (2010.6, 2014.8]      469   814  0.58\n",
       "       (2014.8, 2019.0]      465   877  0.53\n",
       "201910 (1997.979, 2002.2]    240   878  0.27\n",
       "       (2002.2, 2006.4]      252  1486  0.17\n",
       "       (2006.4, 2010.6]      261  1386  0.19\n",
       "       (2010.6, 2014.8]      618  2258  0.27\n",
       "       (2014.8, 2019.0]      493  1391  0.35\n",
       "201911 (1997.979, 2002.2]     85   445  0.19\n",
       "       (2002.2, 2006.4]      121  1007  0.12\n",
       "       (2006.4, 2010.6]      142  1181  0.12\n",
       "       (2010.6, 2014.8]      385  2711  0.14\n",
       "       (2014.8, 2019.0]      276  1818  0.15"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bin_penetration = pd.pivot_table(data,index=['外呼时间','bin'],values=['用户号码','是否参与活动'],aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x:format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "综合三个月情况来看，入网时间较晚的用户渗透率相对较高，处于中早期的用户渗透率相对较低。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 近6个月的平均花费"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(-1, 20]</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>22</td>\n",
       "      <td>56</td>\n",
       "      <td>0.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>655</td>\n",
       "      <td>1221</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 150]</th>\n",
       "      <td>300</td>\n",
       "      <td>530</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(150, 200]</th>\n",
       "      <td>73</td>\n",
       "      <td>128</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 20]</th>\n",
       "      <td>420</td>\n",
       "      <td>1842</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>921</td>\n",
       "      <td>4003</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>445</td>\n",
       "      <td>1364</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 150]</th>\n",
       "      <td>68</td>\n",
       "      <td>163</td>\n",
       "      <td>0.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(150, 200]</th>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 20]</th>\n",
       "      <td>153</td>\n",
       "      <td>1336</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(20, 50]</th>\n",
       "      <td>321</td>\n",
       "      <td>3192</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(50, 100]</th>\n",
       "      <td>427</td>\n",
       "      <td>2148</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 150]</th>\n",
       "      <td>96</td>\n",
       "      <td>444</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(150, 200]</th>\n",
       "      <td>12</td>\n",
       "      <td>42</td>\n",
       "      <td>0.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   参与活动数   用户数   渗透率\n",
       "外呼时间   bin                          \n",
       "201909 (-1, 20]        0     2  0.00\n",
       "       (20, 50]       22    56  0.39\n",
       "       (50, 100]     655  1221  0.54\n",
       "       (100, 150]    300   530  0.57\n",
       "       (150, 200]     73   128  0.57\n",
       "201910 (-1, 20]      420  1842  0.23\n",
       "       (20, 50]      921  4003  0.23\n",
       "       (50, 100]     445  1364  0.33\n",
       "       (100, 150]     68   163  0.42\n",
       "       (150, 200]     10    27  0.37\n",
       "201911 (-1, 20]      153  1336  0.11\n",
       "       (20, 50]      321  3192  0.10\n",
       "       (50, 100]     427  2148  0.20\n",
       "       (100, 150]     96   444  0.22\n",
       "       (150, 200]     12    42  0.29"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['bin'] = pd.cut(data['近6个月平均话费'],bins=[-1,20,50,100,150,200])\n",
    "bin_penetration = pd.pivot_table(data,index=['外呼时间','bin'],values=['用户号码','是否参与活动'],aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x:format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，月平均话费越高的用户，外呼渗透率越提高。**10月和11月外呼用户集中在月平均话费<100元的话费段的用户**，这也导致了渗透率的下降。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 近6个月的平均使用流量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">201909</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>20</td>\n",
       "      <td>49</td>\n",
       "      <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 500]</th>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(500, 1000]</th>\n",
       "      <td>15</td>\n",
       "      <td>42</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1000, 3000]</th>\n",
       "      <td>71</td>\n",
       "      <td>162</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(3000, 5000]</th>\n",
       "      <td>85</td>\n",
       "      <td>192</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(5000, 10000]</th>\n",
       "      <td>199</td>\n",
       "      <td>352</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(10000, 40000]</th>\n",
       "      <td>649</td>\n",
       "      <td>1107</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>591</td>\n",
       "      <td>3726</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>109</td>\n",
       "      <td>408</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 500]</th>\n",
       "      <td>204</td>\n",
       "      <td>697</td>\n",
       "      <td>0.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(500, 1000]</th>\n",
       "      <td>226</td>\n",
       "      <td>743</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1000, 3000]</th>\n",
       "      <td>284</td>\n",
       "      <td>867</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(3000, 5000]</th>\n",
       "      <td>109</td>\n",
       "      <td>272</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(5000, 10000]</th>\n",
       "      <td>125</td>\n",
       "      <td>277</td>\n",
       "      <td>0.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(10000, 40000]</th>\n",
       "      <td>216</td>\n",
       "      <td>409</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>237</td>\n",
       "      <td>2482</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>24</td>\n",
       "      <td>308</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 500]</th>\n",
       "      <td>80</td>\n",
       "      <td>640</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(500, 1000]</th>\n",
       "      <td>108</td>\n",
       "      <td>836</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1000, 3000]</th>\n",
       "      <td>173</td>\n",
       "      <td>1133</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(3000, 5000]</th>\n",
       "      <td>78</td>\n",
       "      <td>454</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(5000, 10000]</th>\n",
       "      <td>98</td>\n",
       "      <td>553</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(10000, 40000]</th>\n",
       "      <td>211</td>\n",
       "      <td>756</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       参与活动数   用户数   渗透率\n",
       "外呼时间   bin                              \n",
       "201909 (-1, 100]          20    49  0.41\n",
       "       (100, 200]          1     6  0.17\n",
       "       (200, 500]         10    27  0.37\n",
       "       (500, 1000]        15    42  0.36\n",
       "       (1000, 3000]       71   162  0.44\n",
       "       (3000, 5000]       85   192  0.44\n",
       "       (5000, 10000]     199   352  0.57\n",
       "       (10000, 40000]    649  1107  0.59\n",
       "201910 (-1, 100]         591  3726  0.16\n",
       "       (100, 200]        109   408  0.27\n",
       "       (200, 500]        204   697  0.29\n",
       "       (500, 1000]       226   743  0.30\n",
       "       (1000, 3000]      284   867  0.33\n",
       "       (3000, 5000]      109   272  0.40\n",
       "       (5000, 10000]     125   277  0.45\n",
       "       (10000, 40000]    216   409  0.53\n",
       "201911 (-1, 100]         237  2482  0.10\n",
       "       (100, 200]         24   308  0.08\n",
       "       (200, 500]         80   640  0.12\n",
       "       (500, 1000]       108   836  0.13\n",
       "       (1000, 3000]      173  1133  0.15\n",
       "       (3000, 5000]       78   454  0.17\n",
       "       (5000, 10000]      98   553  0.18\n",
       "       (10000, 40000]    211   756  0.28"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['bin'] = pd.cut(data['近6个月平均使用流量'], bins=[-1, 100,200, 500, 1000, 3000, 5000, 10000, 40000])\n",
    "bin_penetration = pd.pivot_table(data, index=['外呼时间', 'bin'], values=['用户号码', '是否参与活动'], aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数', '用户数']\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x: format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "总体来看，月平均使用流量越多的用户，渗透率越高。**10月、11月外呼最多的在月平均使用流量<100元的用户**，但是这一部分用户的渗透率相对较低。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 近6个月平均使用语音"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>bin</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201909</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>385</td>\n",
       "      <td>717</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>286</td>\n",
       "      <td>541</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 300]</th>\n",
       "      <td>170</td>\n",
       "      <td>316</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(300, 400]</th>\n",
       "      <td>126</td>\n",
       "      <td>213</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(400, 500]</th>\n",
       "      <td>83</td>\n",
       "      <td>150</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201910</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>1113</td>\n",
       "      <td>4247</td>\n",
       "      <td>0.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>422</td>\n",
       "      <td>1823</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 300]</th>\n",
       "      <td>196</td>\n",
       "      <td>793</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(300, 400]</th>\n",
       "      <td>88</td>\n",
       "      <td>352</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(400, 500]</th>\n",
       "      <td>45</td>\n",
       "      <td>184</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">201911</th>\n",
       "      <th>(-1, 100]</th>\n",
       "      <td>470</td>\n",
       "      <td>4038</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(100, 200]</th>\n",
       "      <td>247</td>\n",
       "      <td>1636</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(200, 300]</th>\n",
       "      <td>158</td>\n",
       "      <td>808</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(300, 400]</th>\n",
       "      <td>88</td>\n",
       "      <td>433</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(400, 500]</th>\n",
       "      <td>46</td>\n",
       "      <td>247</td>\n",
       "      <td>0.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   参与活动数   用户数   渗透率\n",
       "外呼时间   bin                          \n",
       "201909 (-1, 100]     385   717  0.54\n",
       "       (100, 200]    286   541  0.53\n",
       "       (200, 300]    170   316  0.54\n",
       "       (300, 400]    126   213  0.59\n",
       "       (400, 500]     83   150  0.55\n",
       "201910 (-1, 100]    1113  4247  0.26\n",
       "       (100, 200]    422  1823  0.23\n",
       "       (200, 300]    196   793  0.25\n",
       "       (300, 400]     88   352  0.25\n",
       "       (400, 500]     45   184  0.24\n",
       "201911 (-1, 100]     470  4038  0.12\n",
       "       (100, 200]    247  1636  0.15\n",
       "       (200, 300]    158   808  0.20\n",
       "       (300, 400]     88   433  0.20\n",
       "       (400, 500]     46   247  0.19"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['bin'] = pd.cut(data['近6个月平均使用语音'],bins=[-1,100,200,300,400,500])\n",
    "bin_penetration = pd.pivot_table(data,index=['外呼时间','bin'],values=['用户号码','是否参与活动'],aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x:format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不同月套餐使用语音段，用户的渗透率并无明显差异。也就是说, 通过语音时长和渗透率基本没关系"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 号码品牌"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>参与活动数</th>\n",
       "      <th>用户数</th>\n",
       "      <th>渗透率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>外呼时间</th>\n",
       "      <th>号码品牌</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">201909</th>\n",
       "      <th>3G</th>\n",
       "      <td>58</td>\n",
       "      <td>110</td>\n",
       "      <td>0.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4G</th>\n",
       "      <td>992</td>\n",
       "      <td>1827</td>\n",
       "      <td>0.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">201910</th>\n",
       "      <th>2G</th>\n",
       "      <td>332</td>\n",
       "      <td>2890</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3G</th>\n",
       "      <td>31</td>\n",
       "      <td>370</td>\n",
       "      <td>0.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4G</th>\n",
       "      <td>1501</td>\n",
       "      <td>4139</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">201911</th>\n",
       "      <th>2G</th>\n",
       "      <td>140</td>\n",
       "      <td>1979</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3G</th>\n",
       "      <td>65</td>\n",
       "      <td>420</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4G</th>\n",
       "      <td>804</td>\n",
       "      <td>4763</td>\n",
       "      <td>0.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             参与活动数   用户数   渗透率\n",
       "外呼时间   号码品牌                   \n",
       "201909 3G       58   110  0.53\n",
       "       4G      992  1827  0.54\n",
       "201910 2G      332  2890  0.11\n",
       "       3G       31   370  0.08\n",
       "       4G     1501  4139  0.36\n",
       "201911 2G      140  1979  0.07\n",
       "       3G       65   420  0.15\n",
       "       4G      804  4763  0.17"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bin_penetration = pd.pivot_table(data,index=['外呼时间','号码品牌'],values=['用户号码','是否参与活动'],aggfunc=['count'])\n",
    "bin_penetration = bin_penetration['count']\n",
    "bin_penetration.columns = ['参与活动数','用户数']\n",
    "bin_penetration['渗透率'] = (bin_penetration['参与活动数']/bin_penetration['用户数']).map(lambda x:format(x,'.2f'))\n",
    "bin_penetration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "整体来看，随着号码品牌的提高，渗透率也逐渐提高。9月的渗透率稍高于10月和11月。而渗透率较高的4G用户数则大多来自10月、11月，因此**选取了不同号码品牌的用户并不是渗透率下降的原因**。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户的哪些特征因素对用户办理视频会员的影响大？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 影响因素的重要性排序"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用决策树的分类方法，找到各个用户特征对其参与活动与否的影响程度大小。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.tree import DecisionTreeClassifier\n",
    "#获取外呼分钟数大于0的行数据，作为建模的样本数据\n",
    "#只针对接通电话的用户探究各个用户特征对参与活动的影响程度\n",
    "data_new = data.loc[data.外呼分钟数>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "#样本提取\n",
    "x = data_new.loc[:,['用户套餐月租', '入网时间', '近6个月平均话费', '近6个月平均使用流量', \n",
    "                    '近6个月平均使用语音', '号码品牌', '用户年龄', '用户性别', \n",
    "                    '外呼时间', '外呼分钟数']]\n",
    "y = data_new['是否参与活动']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "#特征值化\n",
    "x['号码品牌'] = x['号码品牌'].map({'2G':0,'3G':1,'4G':2})\n",
    "x['用户性别'] = x['用户性别'].map({'女':0,'男':1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#标签数据y中的NaN表示没有参与活动，将其填充为0，非空元素转变为1\n",
    "y = y.fillna(0)\n",
    "y = y.map(lambda x: 1 if x != 0 else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "用户性别          0.009888\n",
       "外呼时间          0.024797\n",
       "用户套餐月租        0.057988\n",
       "用户年龄          0.070714\n",
       "号码品牌          0.073580\n",
       "近6个月平均使用流量    0.087360\n",
       "近6个月平均使用语音    0.093297\n",
       "近6个月平均话费      0.093922\n",
       "入网时间          0.113762\n",
       "外呼分钟数         0.374692\n",
       "dtype: float64"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#建模\n",
    "model = DecisionTreeClassifier()\n",
    "model.fit(x, y)\n",
    "\n",
    "#获取模型计算出来的各个特征的重要程度\n",
    "importances = model.feature_importances_\n",
    "#将其转变为Series显示\n",
    "importances = pd.Series(importances, index=x.columns)\n",
    "#对重要程度进行排序\n",
    "importances = importances.sort_values(ascending = True)\n",
    "importances"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "总体来看，对用户参与活动、办理视频会员影响最大的还是外呼分钟数，其次才是入网时间、语音、流量使用情况等用户维度的因素。总体来看，外呼质量和用户特征均对用户参与活动有一定的影响。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 总结与建议\n",
    "\n",
    "本次活动渗透率逐月下降的关键问题及背后的原因可以归纳如下：\n",
    "\n",
    "**10月关键问题**：有效接通率、接通渗透率都有所下滑；\n",
    "\n",
    "- 用户选取上 : 选取的高龄用户、月租套餐低、月平均使用流量、话费少的用户过多；\n",
    "- 在外呼用户数量大幅增大的同时，外呼通话质量有所下降，通话时间明显缩短。\n",
    "\n",
    "**11月关键问题**：有效接通率、接通渗透率都有所下滑，但接通渗透率有所回升；\n",
    "\n",
    "- 用户选取上 : 选取的高龄用户、月租套餐低、月平均使用流量、话费少的用户过多；\n",
    "- 外呼策略上出了问题，可能选取了不合适的时间段进行外呼；\n",
    "- 与10月相比，接通数急剧减少，因此外呼通话质量有所提升，接通渗透率回升；\n",
    "\n",
    "通过分析，可以提出以下几点建议：\n",
    "\n",
    "1. 首先得正确选择外呼时间段，确保接通率；因为通话时长是影响用户办理的最关键因素，但在此之前得确保用户接听电话；\n",
    "2. 最关键的是尽量延长通话时间（最好在50秒以上）；\n",
    "3. 多选取号码品牌为3G、4G，年龄较小、入网时间较晚，流量开销大，语音通话少，平均话费较高的用户。\n",
    "4. 加强对外呼团队的培训和考核，确保通话质量和通话时长。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
